package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import jdbc.ConnectionFactory;
import Modelo.Locacao;

public class LocacaoPostgresDAO implements LocacaoDAO{
	//
	// ATRIBUTOS
	//
	private Connection cnn;
	private String sql;
	private PreparedStatement stmt;
	private ResultSet rs;
	private Locacao l;

	//
	// CONTRUTOR
	//
	public LocacaoPostgresDAO() {
		conectar();
	}

	//
	// INSERT
	//	
	public boolean adicionar(Locacao l) {
		this.sql = "INSERT INTO Locacao (dataHoraEmprestimo, dataHoraDevolucao, localEmprestimo, localPrevistoDevolucao, " +
				"tipoTarifa, status, placa, cpf, cnpj) VALUES (?,?,?,?,?,?,?,?,?)";
		conectar();
		
		try {
			stmt = cnn.prepareStatement(sql);

			stmt.setString(1, l.getDataEmpre());
			stmt.setString(2, l.getDataDev());
			stmt.setString(3, l.getLocalEmpre());
			stmt.setString(4, l.getLocalPrevDev());
			stmt.setString(5, l.getTipoTarifa());
			stmt.setString(6, l.getStatus());
			stmt.setString(7, l.getPlaca());
			stmt.setString(8, l.getCpf());
			stmt.setString(9, l.getCnpj());

			stmt.execute();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			}
			return false;
		} finally {
			fechar();
		}
	}
	
	//
	// PESQUISAS
	//
	public boolean validaLocacao(int id) throws SQLException {
		this.sql = "SELECT * FROM Locacao WHERE idLocacao=?";
		conectar();
		
		try {
			stmt = cnn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next())
				return true;
			else
				return false;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			}
			return false;
		} finally {
			fechar();
		} 
	}
	
	// Pesquisar por ID
	public Locacao pesquisar(int id) throws SQLException {
		this.sql = "SELECT * FROM Locacao WHERE idLocacao=?";
		l = new Locacao();
		conectar();
		
		try {
			stmt = cnn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				l.setDataDev(rs.getString("dataHoraDevolucao"));
				l.setDataEmpre(rs.getString("dataHoraEmprestimo"));
				l.setLocalEmpre(rs.getString("localEmprestimo"));
				l.setLocalPrevDev(rs.getString("localPrevistoDevolucao"));
				l.setTipoTarifa(rs.getString("tipoTarifa"));
				l.setPlaca(rs.getString("placa"));
				l.setStatus(rs.getString("status"));
			}
			rs.close();
			return l;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			} 
		} finally {
			fechar();
		}
		return null;
	}
	
	// Pesquisar por placa
	public Locacao pesquisar(String placa) throws SQLException {
		this.sql = "SELECT * FROM Locacao WHERE placa=?";
		l = new Locacao();
		conectar();
		
		try {
			stmt = cnn.prepareStatement(sql);
			stmt.setString(1, placa);
			rs = stmt.executeQuery();
			while (rs.next()) {
				l.setIdlocacao(rs.getInt("idLocacao"));
				l.setDataDev(rs.getString("dataHoraDevolucao"));
				l.setDataEmpre(rs.getString("dataHoraEmprestimo"));
				l.setLocalEmpre(rs.getString("localEmprestimo"));
				l.setLocalPrevDev(rs.getString("localPrevistoDevolucao"));
				l.setTipoTarifa(rs.getString("tipoTarifa"));
				l.setPlaca(rs.getString("placa"));
				l.setStatus(rs.getString("status"));
			}
			rs.close();
			return l;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			}
			
		} finally {
			fechar();
		}
		return null;
	}
	
	//
	// UPDATE
	//
	public boolean devolver(int id) {
		this.sql = "update Locacao set status=? where idLocacao=?";
		conectar();
		
		try {
			stmt = cnn.prepareStatement(sql);
			
			stmt.setString(1, "Devolvido");
			stmt.setInt(2, id);
			stmt.execute();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				cnn.rollback();
			} catch (SQLException e1) {
				System.out.print(e1.getStackTrace());
			}
			return false;
		} finally {
			fechar();
		}
	}
	
	//
	// CONECTAR
	//
	private void conectar() {
		this.cnn = new ConnectionFactory().getConnection();
	}

	//
	// FECHAR
	//
	private void fechar() {
		try {
			stmt.close();
			cnn.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}
